package org.NooLab.texx.storage;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.List;

   
import org.NooLab.docobserver.structures.DocumentObserverIntf;
import org.NooLab.itexx.storage.ConnectorClientIntf;
import org.NooLab.itexx.storage.DatabaseMgmt;
import org.NooLab.itexx.storage.DbConnector;
import org.NooLab.itexx.storage.DbFieldRequests;
import org.NooLab.itexx.storage.DbLogin;
import org.NooLab.itexx.storage.docserver.storage.DataBaseHandler;
import org.NooLab.itexx.storage.docserver.storage.DataBaseHandlerIntf;
import org.NooLab.itexx.storage.docserver.storage.DocoDataBaseIntf;
import org.NooLab.itexx.storage.docserver.storage.MetaData;
import org.NooLab.itexx.storage.docserver.storage.ObservedDocuments;
import org.NooLab.itexx.storage.docserver.storage.db.iciql.Documents;
import org.NooLab.itexx.storage.docserver.storage.db.iciql.Folders;
import org.NooLab.texx.properties.TexxProperties;
import org.NooLab.utilities.files.DFutils;
import org.NooLab.utilities.logging.PrintLog;
import org.h2.tools.Server;

import com.iciql.Db;


/**
 * 
 * 
 *
 */
public class DocoDataBase implements DocoDataBaseIntf ,ConnectorClientIntf{

	
	TexxProperties texxProperties;
	
	DataBaseHandlerIntf dbHandler ;
	private DbConnector dbConnector;

	String databaseName = "docoserv";
	String storageDir = "" ; 
	
	String databaseUrl="";

	String accessMode = "tcp"; // "http" = external server via 8082, "file" 
	Server server;

	Connection connection;
	Db iciDb;
	DatabaseMetaData jdbMetaData ;
	String dbCatalog ;
	MetaData metaData ;
	
	String user = "sa";
	String password = "sa" ;
	
	String databaseFile="";
	
	DFutils fileutil = new DFutils();
	PrintLog out = new PrintLog(2,false);
	
	// ========================================================================
	public DocoDataBase( TexxProperties texxProperties){
		// docoProperties = props ;
		// sqls = new SQLstatements(null); 
		this.texxProperties = texxProperties;
		dbConnector = new DbConnector((ConnectorClientIntf)this);
		
		getStorageDir();
		out.setPrefix("[Texx-Doco]");
	}
	// ========================================================================
	
	@Override
	public Connection getConnection() {
		
		return connection;
	}

	@Override
	public Db getIciDb() {
		
		return iciDb;
	}

	@Override
	public String getDatabaseFile() {
		return databaseFile;
	}
 
	
	protected void getJdbMeta(Connection c) throws SQLException{
		jdbMetaData = c.getMetaData();
		dbCatalog = c.getCatalog() ;
		
	}
	
	@Override
	public void setDbMetaData(DatabaseMetaData dbMetaData) {
		jdbMetaData = dbMetaData;		
	}

	
	@Override
	public String getDatabaseName() {
		return databaseName;
	}

	@Override
	public String getStorageDir() {

		try{

			String systemroot = texxProperties.getSystemRootDir();   // [path]/iTexx  
			//if (docoProperties.getApplicationContext()
			//    .contentEquals( DocumentObserverProperties._APP_CONTEXT_ITEXX)){
			
			String appcontext = texxProperties.getApplicationContext() ; 
			
			if (appcontext.contains(TexxProperties._APP_CONTEXT_ITEXX)){ 
				// itexx (as service within iTexx) or "standalone" (via applet)
				String storageroot = systemroot;
				
				if (storageroot.contains("/DocumentServer")){
					storageroot = DFutils.getParentDir(storageroot, 2);
				}
				
				storageDir = DFutils.createPath(storageroot, "storage/") ;
				
			}else{
				// the storage is subject of the project
				String prj = texxProperties.getPersistenceSettings().getProjectName() ;
				storageDir = DFutils.createPath(systemroot, prj+"/") ;
				storageDir = DFutils.createPath(storageDir, "storage/") ;
			}
			
			

		}catch(Exception e){
			
		}
		return storageDir ; 
	}
 
	@Override
	public void setStorageFolder(String dir) {
		storageDir = dir;
	}


	
	public void updateInfraStructure(Connection c ) throws Exception{

		getJdbMeta(c);
		
		dbHandler.setiDb( iciDb ) ;

		// creates a new instance for new MetaData and retrieves the latest info again
		dbHandler.updateMetaData(c) ; 
	
	}
	
	public void open() throws Exception{
		
		iciDb = Db.open(databaseUrl, user, password);
		connection = iciDb.getConnection();

		updateInfraStructure(connection);
	}
	
	@Override
	public void open(Connection c) throws Exception {
		iciDb = Db.open(c);
		updateInfraStructure(c);
	}

	@Override
	public Connection getWireLessDatabaseServer(String string, String string2) {
		String url;
		String h2Dir ,storDir;
		
		Connection c = null;
		
		try {
			// "jdbc:h2:"+
			// System.setProperty("h2.baseDir", storageDir);

			
			h2Dir = DatabaseMgmt.setH2BaseDir( storageDir, 0); //DatabaseMgmt._BASEDIR_QUERY_STOR);
			// String storDir = storageDir;
			storDir = DFutils.createPath( h2Dir, "storage/") ;
			
			if (storDir.endsWith("/")){
				storDir = storDir.substring(0,storDir.length()-1); 
			}
			url = 	"jdbc:h2:tcp://localhost/"+(storDir)+"/"+databaseName ;
			
			DbLogin login = new DbLogin(user,password) ; 
			c = dbConnector.getConnection(url, login);
			connection = c;
			// Class h2Driver = Class.forName("org.h2.Driver");
			// c = DriverManager.getConnection( url, user, password);
			// connection = c;
			if (c!=null){
				databaseUrl = url ;
			}
			dbHandler = new DataBaseHandler( this ) ;
			
		}catch(Exception e){
			e.printStackTrace() ;
		}
		return c;
	}

	/**
	 * 
	 * 
	 * @param maxCount
	 * @param mode  0=file with changed content, 1=moved file,  2=renamed file,  -1=deleted file
	 * @return
	 */
	@Override
	public ObservedDocuments getDocumentsDue( int maxCount, int mode,
											  DbFieldRequests accvalues) {

		ObservedDocuments docs = new ObservedDocuments ();  
		// ArrayList<String> docs = new ArrayList<String> ();
		
		String filename, filepath ; 
		
		Documents d = new Documents(), dr ;
		Folders   f = new Folders();
		
		List<Documents> docRecords = null;
		List<Folders> folderRecords= null;
		List<String> folderNames = null;

		
		try{
			if (connection.isClosed()==false){
				open(connection) ; // defines also iciDb...
			}else{
				open();
			}

			try{
				
				if (mode == 0){
					docRecords = iciDb.from(d).where(d.status).is( DocumentObserverIntf._STATUS_FILE_REGISTERED ) // 1
											     .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_MOD ) // 9
											     .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_NEW)  // 10
											     .select();
					/*
					String sql = iciDb.from(d).where(d.status).is( DocumentObserverIntf._STATUS_FILE_REGISTERED ) // 1
				     							 .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_MOD ) // 2
				     							 .or(d.status).is( DocumentObserverIntf._FILE_CHANGE_NEW).toSQL();
				    sql = sql+" "; out.print(2, "\n"+sql+"\n");
				    */ 
				}
				if (mode == 1){
					docRecords = iciDb.from(d).where(d.status) 
											  .is( DocumentObserverIntf._FILE_CHANGE_MOVE ) // 11
											  .select();
				}
				if (mode == 2){
					docRecords = iciDb.from(d).where(d.status) 
											  .is( DocumentObserverIntf._FILE_CHANGE_RENAME ) // 12
											  .select();
				}
				if (mode == -1){
					docRecords = iciDb.from(d).where(d.status)
											  .is( DocumentObserverIntf._FILE_CHANGE_DEL )
											  .select();
					// also asking orphanes
					
				}
				docs.clear();
				if (docRecords!=null){
					
					if (mode>=0){
						for (int i = 0; i < docRecords.size(); i++) {
							dr = docRecords.get(i);
							filename = dr.name;

							String hostFolder = iciDb.from(f).where(f.id).is(dr.folderid).select().get(0).location;

							filepath = DFutils.createPath(hostFolder, filename);
							
							if (DFutils.fileExists(filepath)) {
								docs.add( dr.docid, filepath);
								
								iciDb.from(d).set(d.status).to(DocumentObserverIntf._STATUS_FILE_HANDLING)
								             .where(d.docid).is(dr.docid)
								             .update();  
							}
							if (docs.size()>=maxCount){
								break;
							}
						}// i->
						connection.commit() ; // make it for all users !!
					}
				} // any result
				
				connection.close();
				
			}catch(Exception e){
				e.printStackTrace();
			}

		}catch(Exception e){
			e.printStackTrace();
		}
		
		
		return docs;

	}
 
	 
	
	
	
	
}
